I have a requirement in oracle to replace the special characters at first and last position of the column data.
Requirement: only [][.,$'*&!%^{}-?] and alphanumberic characters are allowed to stay in the address data and rest of the characters has to be replaced with space.I have tried in below way in different probabilities but its not working as expected. Please help me in resolving this.
SELECT emp_address,
REGEXP_REPLACE(
emp_address,
'^[^[[][.,$'\*&!%^{}-?\]]]|[^[[][.,$'\*&!%^{}-?\]]]$'
) AS simplified_emp_address
FROM table_name
MT0 :
As per the regular expression operators and metasymbols documentation:\n\nPut ] as the first character of the (negated) character group;\n- as the last; and\nDo not put . immediately after [ or it can be matched as the start of a coalition element [..] if there is a second . later in the expression.\n\nAlso:\n\nDouble up the single quote (to escape it, so it does not terminate the string literal); and\nInclude the non-special characters a-zA-Z0-9 in the capture group too otherwise they will be matched.\n\nWhich gives you the regular expression:\nSELECT emp_address,\n REGEXP_REPLACE(\n emp_address,\n '^[^][,.$''\\*&!%^{}?a-zA-Z0-9-]|[^][,.$''\\*&!%^{}?a-zA-Z0-9-]$'\n ) AS simplified_emp_address\nFROM table_name\n\nWhich, for the sample data:\nCREATE TABLE table_name (emp_address) AS\nSELECT '"test1"' FROM DUAL UNION ALL\nSELECT '$test2$' FROM DUAL UNION ALL\nSELECT '[test3]' FROM DUAL UNION ALL\nSELECT 'test4' FROM DUAL UNION ALL\nSELECT '|test5|' FROM DUAL;\n\nOutputs:\n\n\n\n\n\nEMP_ADDRESS\nSIMPLIFIED_EMP_ADDRESS\n\n\n\n\n"test1"\ntest1\n\n\n$test2$\n$test2$\n\n\n[test3]\n[test3]\n\n\ntest4\ntest4\n\n\n|test5|\ntest5\n\n\n",
2022-02-11T09:07:42